﻿using System;
using System.Data;
using System.Configuration;



using System.IO;
using System.Globalization;
using System.Text.RegularExpressions;

using BUS;

using System.Collections.Generic;
using FlexCel.Core;
using FlexCel.Report;
using FlexCel.XlsAdapter;
using FlexCel.Render;

namespace PEC
{
    public class ReportGenerator
    {
        string _FilterPara = "FilterPara";

        public string FilterPara
        {
            get { return _FilterPara; }
            set { _FilterPara = value; }
        }
        public static string __connectString = string.Empty;
        public string QdCode
        {
            get { return _qdCode; }
            set { _qdCode = value; }
        }
        string _qdCode = "", _database = "", _pathTemplate = string.Empty, _pathReport = string.Empty, _name = string.Empty;

        ExcelFile _xlsFile;
        DataSet _dtSet = null;

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }
        
        private void LoadUdfs(ExcelFile Xls)
        {
            //Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, new TT_XLB_EB());
        }
        private ExcelFile AddData(ExcelFile Xls)
        {
            LoadUdfs(Xls);
            return Xls;
        }
        private ExcelFile Run_templatereport(FlexCelReport flexcelreport)
        {            
            object misValue = System.Reflection.Missing.Value;

            String filename = _pathTemplate + _qdCode + ".template.xls";
            if (!File.Exists(filename))
            {
                throw new Exception("Template Report is not exist!");
                // return null;
            }
            ExcelFile result = new XlsFile(filename, true);
            flexcelreport.Run(result);
            return result;
        }
      
        #region Method
        public void GenTemplate(string path_template)
        {
            if (!File.Exists(path_template))
            {
                XlsFile xlsTemp = new XlsFile(_pathTemplate + "-.template.xls");
                xlsTemp.SetCellValue(xlsTemp.GetSheetIndex("<#Config>"), 10, 2, _qdCode, 0);
                xlsTemp.SetCellValue(xlsTemp.GetSheetIndex("<#Config>"), 17, 2, "FilterPara", 0);

                xlsTemp.Save(_pathTemplate + _qdCode + ".template.xls");
            }
        }
        public ReportGenerator(DataSet dtSet, string qdCode, string database, string connectString, string pathTemplate, string pathReport)
        {
            _qdCode = qdCode;
            _database = database;
            _dtSet = dtSet;
            __connectString = connectString;
            _pathReport = pathReport;
            _pathTemplate = pathTemplate;
        }
     
        public ExcelFile ExportExcel(string path)
        {
            _xlsFile = CreateReport();
            return _xlsFile;
        }
        public string ExportExcelToPath(string path)
        {
            String filename = path + "\\" + _qdCode + ".xls";
            _xlsFile = CreateReport();
            _xlsFile.Save(filename);
            return filename;
        }
        public string ExportExcelToFile(string path, string filename)
        {
            //String filename = path + "\\" + _qdCode + ".xls";
            string file = path + filename;
            _xlsFile = CreateReport();
            _xlsFile.Save(file);
            return file;
        }
        public ExcelFile CreateReport()
        {
            FlexCelReport flexcelreport = new FlexCelReport();
            GenTemplate(_pathTemplate + _qdCode + ".template.xls");
            
            //flexcelreport.UserTable += new UserTableEventHandler(flexcelreport_UserTable);
            flexcelreport.AddTable(_dtSet);
            AddReportVariable(flexcelreport);
            ExcelFile rs = Run_templatereport(flexcelreport);
            rs = AddData(rs);
            _xlsFile = rs;
            return rs;
        }

        private void AddReportVariable(FlexCelReport flexcelreport)
        {
            flexcelreport.SetValue("Date", DateTime.Now.ToShortDateString());
            flexcelreport.SetValue("Time", DateTime.Now.ToShortTimeString());
            flexcelreport.SetValue("Name", _name);
            flexcelreport.SetValue("Code", _qdCode);
            flexcelreport.SetValue("DB", _database);
        }
        public MemoryStream ExportPDF(string path)
        {
            MemoryStream ms = new MemoryStream();
            if (_xlsFile == null)
                _xlsFile = CreateReport();

            try
            {
                using (FlexCelPdfExport pdf = new FlexCelPdfExport())
                {
                    pdf.Workbook = _xlsFile;

                    pdf.BeginExport(ms);
                    pdf.ExportAllVisibleSheets(false, "test");
                    pdf.EndExport();
                }
                return ms;
            }
            catch
            {
                return null;
            }

        }
        public MemoryStream ExportPDF(ExcelFile xlsFile)
        {
            MemoryStream ms = new MemoryStream();
            try
            {
                using (FlexCelPdfExport pdf = new FlexCelPdfExport())
                {
                    pdf.Workbook = xlsFile;

                    pdf.BeginExport(ms);
                    pdf.ExportAllVisibleSheets(false, "test");
                    pdf.EndExport();
                }
                return ms;
            }
            catch
            {
                return null;
            }

        }
        public string ExportPDFToPath(string path)
        {
            String filename = path + "\\" + _qdCode + ".pdf";
            //FileStream file = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (_xlsFile == null)
                _xlsFile = CreateReport();

            try
            {
                using (FlexCelPdfExport pdf = new FlexCelPdfExport())
                {
                    pdf.Workbook = _xlsFile;
                    using (MemoryStream ms = new MemoryStream())
                    {
                        pdf.BeginExport(ms);
                        pdf.ExportAllVisibleSheets(false, "test");
                        pdf.EndExport();

                        pdf.Export(filename);
                    }
                }
                return filename;
            }
            catch
            {
                return "";
            }

        }
        public string ExportHTMLToPath(string path)
        {
            String filename = path + _qdCode + ".htm";
            //TextWriter file = new StringWriter(); ;
            if (_xlsFile == null)
                _xlsFile = CreateReport();
            try
            {
                using (FlexCelHtmlExport html = new FlexCelHtmlExport())
                {
                    html.Workbook = _xlsFile;
                    html.AllowOverwritingFiles = true;
                    html.SavedImagesFormat = THtmlImageFormat.Png;

                    html.Export(filename, "images");

                }
                return filename;
            }
            catch
            {
                return "";
            }
        }
        public TextWriter ExportHTML(string path)
        {
            String filename = path + _qdCode + ".html";
            TextWriter file = new StringWriter(); ;
            if (_xlsFile == null)
                _xlsFile = CreateReport();
            try
            {
                using (FlexCelHtmlExport html = new FlexCelHtmlExport())
                {
                    html.Workbook = _xlsFile;
                    using (MemoryStream ms = new MemoryStream())
                    {
                        //if (File.Exists(filename))
                        //    File.Delete(filename);
                        string pathx = Path.GetDirectoryName(filename);
                        string name = Path.GetFileNameWithoutExtension(filename);
                        string ext = ".png";
                        string fileimage = pathx + "\\" + name + "_image1" + ext;
                        if (File.Exists(fileimage))
                            File.Delete(fileimage);

                        html.Export(file, filename, null);

                        //file.ToString();
                    }
                }
                return file;
            }
            catch
            {
                return null;
            }

        }
        public TextWriter ExportHTML(string path, ExcelFile xlsFile)
        {
            //String filename = path + _pathTemplate + "\\" + _database + "\\" + _qdCode + ".html";
            TextWriter file = new StringWriter(); ;
            try
            {
                using (FlexCelHtmlExport html = new FlexCelHtmlExport())
                {
                    html.Workbook = xlsFile;
                    using (MemoryStream ms = new MemoryStream())
                    {
                        //if (File.Exists(filename))
                        //    File.Delete(filename);
                        html.Export(file, "", null);
                        //file.ToString();
                    }
                }
                return file;
            }
            catch
            {
                return null;
            }

        }
        #endregion Method      
    }
}
